﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlBackupAndRestore
{
    public class DbBackupHelper
    {
        /// <summary>
        /// 还原数据库
        /// </summary>
        public static bool RestoreDataBase(string connectionString, string dataBaseName, string dataBaseBackupFile)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                //comm.CommandText = "use master;restore database @DataBaseName From disk = @BackupFile with replace;";
                // alter database不支持参数化语法
                comm.CommandText = "use master;alter database " + dataBaseName + " set offline with rollback immediate; restore database " + dataBaseName + " from disk='" + dataBaseBackupFile + "' with replace;alter database  " + dataBaseName + " set online with rollback immediate";
                //comm.CommandText = "use master;alter database @dataBaseName set offline with rollback immediate; restore database @dataBaseName from disk='@backupFile' with replace;alter database  @dataBaseName set online with rollback immediate";
                //comm.Parameters.Add(new SqlParameter("dataBaseName", SqlDbType.NVarChar));
                //comm.Parameters["dataBaseName"].Value = dataBaseName;
                //comm.Parameters.Add(new SqlParameter("backupFile", SqlDbType.NVarChar));
                //comm.Parameters["backupFile"].Value = dataBaseBackupFile;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();
            }
            return true;
        }

        /// <summary>
        /// 备份SqlServer数据库
        /// </summary>
        public static bool BackupDataBase(string connectionString, string dataBaseName, string backupPath, string backupName)
        {
            string filePath = Path.Combine(backupPath, backupName);
            if (File.Exists(filePath))
            {
                File.Delete(filePath);
            }

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "use master;backup database @dbname to disk = @backupname;";
                comm.Parameters.Add(new SqlParameter("dbname", SqlDbType.NVarChar));
                comm.Parameters["dbname"].Value = dataBaseName;
                comm.Parameters.Add(new SqlParameter("backupname", SqlDbType.NVarChar));
                comm.Parameters["backupname"].Value = filePath;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();
            }
            return true;
        }

    }
}
